﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.IO;

namespace WritingHugeFilesIntoDatabase
{
    class WritingHugeFilesIntoDatabase
    {
        const int BUFFER_SIZE = 1048576;

        static SqlConnection OpenDatabaseConnection(string database)
        {
            string connectionString = @"Server=.\SQL_SERVER_2008;" +
            "Database = " + database + "; Integrated Security = true";
            SqlConnection connection = new SqlConnection(connectionString);

            return connection;
        }

        static void InsertRecordIntoDB(SqlConnection connection, string videoFilename)
        {
            FileStream inputFile = File.Open(videoFilename, FileMode.Open);

            using (inputFile)
            {
                byte[] buffer = new byte[BUFFER_SIZE];
                int readBytes = inputFile.Read(buffer, 0, buffer.Length);
                string insertByte = @"INSERT INTO Videos1(VideoFile)
                                VALUES(@info)";
                SqlCommand insertByteCommand = new SqlCommand(insertByte, connection);
                insertByteCommand.Parameters.Add(new SqlParameter("@info", buffer));
                insertByteCommand.ExecuteNonQuery();

                SqlCommand cmdSelectIdentity =
                 new SqlCommand("SELECT @@Identity", connection);
                int insertedRecordId =
                    (int)(decimal)cmdSelectIdentity.ExecuteScalar();


                string update = @"[dbo].[usp_UpdateFile] @bytes,@id";
                SqlCommand updateCommand = new SqlCommand(update, connection);
                updateCommand.Parameters.Add(new SqlParameter("@id", insertedRecordId));
                updateCommand.Parameters.Add(new SqlParameter("@bytes", buffer));
                readBytes = inputFile.Read(buffer, 0, buffer.Length);
                while (readBytes > 0)
                {
                    updateCommand.Parameters["@bytes"].Value = buffer;
                    updateCommand.ExecuteNonQuery();
                    readBytes = inputFile.Read(buffer, 0, buffer.Length);
                }
            }
        }

        static void Main(string[] args)
        {
            SqlConnection connection = OpenDatabaseConnection("VideoDB");
            connection.Open();
            using (connection)
            {
                DateTime start = DateTime.Now;
                string videoPath = @"D:\Downloads\Date Night 2010 EXTENDED DVDRip XviD AC3-KiNGDOM\dn.avi";
                InsertRecordIntoDB(connection,videoPath);
                Console.WriteLine(DateTime.Now - start);
            }
            connection.Close();
        }
    }
}
